package cs4280.dao;

import cs4280.bean.House;
import cs4280.jdbc.DB;
import java.util.ArrayList;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.naming.NamingException;
import javax.sql.DataSource;

public class HouseDAO {

    public static enum RESULT {

        OK, DUPLICATE, FAIL
    };

    public ArrayList<House> getHouses() {
        ArrayList<House> houses = new ArrayList<House>();

        try {
            DataSource ds = DB.getDataSource();
            Connection conn = ds.getConnection();
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT [id], [rownumber], [colnumber] FROM [House];");
            while (rs != null && rs.next() != false) {
                House house = new House();
                house.setId(rs.getInt(1));
                house.setRownumber(rs.getInt(2));
                house.setColnumber(rs.getInt(3));
                houses.add(house);
            }
            if (rs != null) {
                rs.close();
            }
            if (stmt != null) {
                stmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        } catch (javax.naming.NamingException ne) {
            /* ignore */
        } catch (SQLException sqle) {
            /* ignore */
        }

        return houses;
    }

    public House getHouse(int houseId) {
        House house = null;

        try {
            DataSource ds = DB.getDataSource();
            Connection conn = ds.getConnection();
            PreparedStatement pstmt = conn.prepareStatement("SELECT [id], [rownumber], [colnumber] FROM [House] WHERE [id] = ?;");
            pstmt.setInt(1, houseId);
            ResultSet rs = pstmt.executeQuery();
            if (rs != null && rs.next() != false) {
                house = new House();
                house.setId(rs.getInt(1));
                house.setRownumber(rs.getInt(2));
                house.setColnumber(rs.getInt(3));
            }
            if (rs != null) {
                rs.close();
            }
            if (pstmt != null) {
                pstmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        } catch (javax.naming.NamingException ne) {
            /* ignore */
        } catch (SQLException sqle) {
            /* ignore */
        }

        return house;
    }

    public ArrayList<House> getHouseStatistics() {
        ArrayList<House> houses = new ArrayList<House>();

        try {
            DataSource ds = DB.getDataSource();
            Connection conn = ds.getConnection();
            PreparedStatement stmt =conn.prepareStatement("SELECT DISTINCT screening.houseid FROM [screening],[booking] WHERE screening.id = booking.screeningid;");
            ResultSet rs=stmt.executeQuery();


            while (rs != null && rs.next() != false) {
                House house = new House();
                //get the movie id with booking record
                int houseid = rs.getInt(1);
                house.setId(houseid);
                PreparedStatement pstmt = conn.prepareStatement("select count(*) as [count] from [screening],[booking] where screening.houseid= "+houseid +" and screening.id=booking.screeningid;");

                ResultSet countSale = pstmt.executeQuery();
                while (countSale != null && countSale.next() != false) {
                int sale = countSale.getInt(1);
                house.setSales(sale);

                houses.add(house);

                }
                if (pstmt != null) {
                    pstmt.close();
                }
                if (countSale != null) {
                    countSale.close();
                }



            }
            if (rs != null) {
                rs.close();
            }
            if (stmt != null) {
                stmt.close();
            }

            if (conn != null) {
                conn.close();
            }
        } catch (javax.naming.NamingException ne) {
            /* ignore */
        } catch (SQLException sqle) {
            /* ignore */
        }

        return houses;
    }

}
